/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2015
 */

package com.jzwl.system.admin.role.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.jzwl.system.admin.role.pojo.Sysrole;
import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.common.id.IdFactory;
import com.jzwl.common.page.PageObject;



@Repository("v2sysroleDao")
public class SysroleDao {
	
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	@Autowired
	private IdFactory idFactory;
	
	public boolean addSysrole(Map<String, Object> map) {
		
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("roleId", idFactory.nextId());
		
		String sql = "insert into v2_plat_role " 
				 + " (name,description,createdTime,status) " 
				 + " values "
				 + " (:name,:description,:createdTime,:status)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	public String getColumns() {
		return ""
				+" roleId as roleId,"
				+" name as name,"
				+" description as description,"
				+" createdTime as createdTime,"
				+" modifiedTime as modifiedTime,"
				+" modifiedCount as modifiedCount,"
				+" status as status"
				;
	}
	

	public PageObject querySysroleList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql="select " + getColumns() + " from v2_plat_role t where 1=1 ";
		
		  	if(null !=map.get("name") && StringUtils.isNotEmpty(map.get("name").toString())){
			  		sql=sql+ " and t.name  LIKE '%" + map.get("name") +"%'";
		  	}
		  	if(null !=map.get("description") && StringUtils.isNotEmpty(map.get("description").toString())){
			  		sql=sql+ " and t.description  = " + map.get("description") +"";
		  	}
		  	if(null !=map.get("createdTime") && StringUtils.isNotEmpty(map.get("createdTime").toString())){
			  		sql=sql+ " and t.createdTime >= " + map.get("createdTimeBegin") +"";
			  		sql=sql+ " and t.createdTime <= " + map.get("createdTimeEnd"  ) +"";
		  	}
		  	if(null !=map.get("modifiedTime") && StringUtils.isNotEmpty(map.get("modifiedTime").toString())){
			  		sql=sql+ " and t.modifiedTime >= " + map.get("modifiedTimeBegin") +"";
			  		sql=sql+ " and t.modifiedTime <= " + map.get("modifiedTimeEnd"  ) +"";
		  	}
		  	if(null !=map.get("modifiedCount") && StringUtils.isNotEmpty(map.get("modifiedCount").toString())){
			  		sql=sql+ " and t.modifiedCount  = " + map.get("modifiedCount") +"";
		  	}
		  	if(null !=map.get("status") && StringUtils.isNotEmpty(map.get("status").toString())){
			  		sql=sql+ " and t.status  = " + map.get("status") +"";
		  	}
					
					
		sql=sql+ " order by roleId ";
		PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
		
		return po;
	}

	public boolean updateSysrole(Map<String, Object> map) {
		
		String sql ="update v2_plat_role set "
				+ " roleId=:roleId,name=:name,description=:description,modifiedTime=:modifiedTime,status=:status "
				+ " where roleId=:roleId";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteSysrole(Map<String, Object> map) {
		
		String sql = "delete from v2_plat_role where roleId in ("+ map.get("roleId")+ " ) ";

		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	@SuppressWarnings("unchecked")
	public Map<String, Object> getByRoleId(Map<String, Object> map) {
		
		String sql = "select " + getColumns() + " from v2_plat_role where roleId = "+ map.get("roleId") + "";
		
		Map<String, Object> resMap = new HashMap<String, Object>();
		
		resMap = baseDAO.queryForMap(sql);
		
		return resMap;
		
	
	}
	
	public String getUserColumns() {
		return ""
				+" t.id as id,"
				+" t.username as username,"
				+" t.password as password,"
				+" t.sex as sex,"
				+" t.mobile as mobile,"
				+" t.createdTime as createdTime,"
				+" t.status as status,"
				+" t.address as address,"
				+" t.citycode as citycode"
				;
	}
	
	public PageObject querySysroleUserList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql = "select " + getUserColumns() + " from v2_busy_shopuser t "
				+ "left join v2_plat_userrole ur on t.id = ur.userId "
				+ "where 1=1 ";

		if (null != map.get("username") && StringUtils.isNotEmpty(map.get("username").toString())) {
			sql = sql + " and t.username  like '%" + map.get("username") + "%' ";
		}
		if (null != map.get("password") && StringUtils.isNotEmpty(map.get("password").toString())) {
			sql = sql + " and t.password  = " + map.get("password") + "";
		}
		if (null != map.get("sex") && StringUtils.isNotEmpty(map.get("sex").toString())) {
			sql = sql + " and t.sex  = " + map.get("sex") + "";
		}
		if (null != map.get("mobile") && StringUtils.isNotEmpty(map.get("mobile").toString())) {
			sql = sql + " and t.mobile  = " + map.get("mobile") + "";
		}
		if (null != map.get("address") && StringUtils.isNotEmpty(map.get("address").toString())) {
			sql = sql + " and t.address  = " + map.get("address") + "";
		}
		if (null != map.get("createdTime") && StringUtils.isNotEmpty(map.get("createdTime").toString())) {
			sql = sql + " and t.createdTime >= " + map.get("createdTimeBegin") + "";
			sql = sql + " and t.createdTime <= " + map.get("createdTimeEnd") + "";
		}
		if (null != map.get("citycode") && StringUtils.isNotEmpty(map.get("citycode").toString())) {
			sql = sql + " and t.citycode  = " + map.get("citycode") + "";
		}
		sql = sql + " and t.status=1 and ur.roleId = "+map.get("roleId");
		sql = sql + " order by id ";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}
	
	public boolean deleteSysroleUser(Map<String, Object> map) {
		
		String sql = "delete from v2_plat_userrole where roleId = :roleId and userId = :userId ";

		return baseDAO.executeNamedCommand(sql, map);
	}
	
	public boolean addSysroleUser(Map<String, Object> map) {
		
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		//map.put("roleId",  Sequence.nextId());
		
		String sql = "insert into v2_plat_userrole " 
				 + " (userId,roleId,createdTime) " 
				 + " values "
				 + " (:userId,:roleId,:createdTime)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	public PageObject querySysroleNoUserList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql = "select * from v2_busy_shopuser WHERE id not in"
				+ "(select t.id AS id from v2_busy_shopuser t "
				+ "left join v2_plat_userrole ur on t.id = ur.userId "
				+ "where 1=1 ";

		
		sql = sql + " and t.status=1 and ur.roleId = "+map.get("roleId")+")";
		sql = sql + " order by id ";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}
	
	public PageObject querySysroleAuthorityList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql = "select t.authorityId,t.authorityName from v2_plat_authority t "
				+ "left join v2_plat_roleauthority ra on t.authorityId = ra.authorityId "
				+ "where 1=1 ";

		if(null !=map.get("authorityName") && StringUtils.isNotEmpty(map.get("authorityName").toString())){
	  		sql=sql+ " and t.authorityName  like '%" + map.get("authorityName") +"%' ";	
		}
		sql = sql + " and ra.roleId = "+map.get("roleId");
		sql = sql + " order by authorityId ";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}
	
	public List querySysroleNoAuthorityList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql = "select * from v2_plat_authority WHERE authorityId not in"
				+ "(select t.authorityId from v2_plat_authority t "
				+ "left join v2_plat_roleauthority ra on t.authorityId = ra.authorityId "
				+ "where 1=1 ";

		if(null !=map.get("authorityName") && StringUtils.isNotEmpty(map.get("authorityName").toString())){
	  		sql=sql+ " and t.authorityName  like '%" + map.get("authorityName") +"%' ";	
		}
		sql = sql + " and ra.roleId = "+map.get("roleId")+")";
		sql = sql + " order by authorityId ";

		//PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return baseDAO.queryForList(sql);
	}
	
	public boolean deleteSysroleAuthotity(Map<String, Object> map) {
		
		String sql = "delete from v2_plat_roleauthority where roleId = :roleId and authorityId = :authorityId ";

		return baseDAO.executeNamedCommand(sql, map);
	}
	
	public boolean addSysroleAuthority(Map<String, Object> map) {
		
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		//map.put("roleId",  Sequence.nextId());
		
		String sql = "insert into v2_plat_roleauthority " 
				 + " (authorityId,roleId,createdTime) " 
				 + " values "
				 + " (:authorityId,:roleId,:createdTime)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}


	public List<Map<String, Object>> getRoleListByUserId(
			Map<String, Object> map) {
		String sql = "select pr.name "
				+ " from v2_plat_userrole pu left join v2_plat_role pr on pu.roleId = pr.roleId "
				+ " where pu.userId = " + map.get("userId");
		List<Map<String,Object>> list = baseDAO.queryForList(sql);
		return  list;
	}
}
